﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NUnit.Framework;
using System.Windows.Forms;
using ICSharpCode.TextEditor;
using ICSharpCode.TextEditor.Gui;
using DBSourceToolsLib;
using DBSourceToolsLib.Composite;
using DBSourceToolsLib.Suggestions;
using ICSharpCode.MSSQLExtensions.SqlParser;


namespace TestICSharpCode.MSSQLExtensions
{
    [TestFixture]
    public class TestSenseBuilder_Select
    {
        Form m_form = new Form();
        DBComposite m_DBComposite;

        [TestFixtureSetUp]
        public void TestFixtureSetup()
        {
            string strProjectFile = Constants.AdventureWorks_ProjectFile;
            DatabaseProject oDBProject = new DatabaseProject(strProjectFile);

            oDBProject.LoadFromDisk(false);
            Assert.AreEqual("AdventureWorks", oDBProject.ProjectName);
            Assert.Greater(oDBProject.SourceDatabases.Count, 0);
            Assert.AreEqual((string)"AdventureWorksLT", oDBProject.SourceDatabases[0].DatabaseName);
            Assert.AreEqual((string)"AdventureWorksLT", oDBProject.SourceDatabases[0].NickName);

            SourceDatabase oSource = oDBProject.SourceDatabases[0];

            System.Windows.Forms.Form oForm = new System.Windows.Forms.Form();

            SourceDatabaseLoader oSourceLoader =
                new SourceDatabaseLoader(oForm,
                    DBSourceToolsLib.Commands.DatabaseCommandTypes.LoadFromDisk);

            oSourceLoader.Execute(ref oSource, null);

            m_DBComposite = oSource.GetDBComposite();

        }


        [TestFixtureTearDown]
        public void TestFixtureTearDown()
        {

        }


        [STAThread]
        [Test]
        public void TestSense_select_with_alias()
        {
            ICSharpCode.MSSQLExtensions.MSSQLTextEditor m_MsSqlTextEditor = new ICSharpCode.MSSQLExtensions.MSSQLTextEditor(m_form);

            SenseBuilder oSenseBuilder = new SenseBuilder(m_MsSqlTextEditor.Document);
            oSenseBuilder.SetComposite(m_DBComposite);

            string strText = "select *, from BuildVersion bv";

            m_MsSqlTextEditor.Text = strText;

            DateTime oDtStart = DateTime.Now;
            List<Suggestion> oSenseList = oSenseBuilder.GetSuggestionsForCursorAt(9);

            List<string> lstStrings = oSenseList.Select(sli => sli.Name).ToList();
            foreach (string strItem in lstStrings)
                Console.WriteLine(strItem);

            Assert.True(lstStrings.Contains("bv.ModifiedDate"));

        }

        [STAThread]
        [Test]
        public void TestSense_select_with_alias_two()
        {
            ICSharpCode.MSSQLExtensions.MSSQLTextEditor m_MsSqlTextEditor = new ICSharpCode.MSSQLExtensions.MSSQLTextEditor(m_form);

            SenseBuilder oSenseBuilder = new SenseBuilder(m_MsSqlTextEditor.Document);
            oSenseBuilder.SetComposite(m_DBComposite);

            string strText = "select *, from BuildVersion bv, BuildVersion build";

            m_MsSqlTextEditor.Text = strText;

            DateTime oDtStart = DateTime.Now;
            List<Suggestion> oSenseList = oSenseBuilder.GetSuggestionsForCursorAt(9);

            List<string> lstStrings = oSenseList.Select(sli => sli.Name).ToList();
            foreach (string strItem in lstStrings)
                Console.WriteLine(strItem);

            Assert.True(lstStrings.Contains("bv.ModifiedDate"));
            Assert.True(lstStrings.Contains("build.ModifiedDate"));

        }

        [STAThread]
        [Test]
        public void TestSense_select_with_alias_two_tables()
        {
            ICSharpCode.MSSQLExtensions.MSSQLTextEditor m_MsSqlTextEditor = new ICSharpCode.MSSQLExtensions.MSSQLTextEditor(m_form);

            SenseBuilder oSenseBuilder = new SenseBuilder(m_MsSqlTextEditor.Document);
            oSenseBuilder.SetComposite(m_DBComposite);

            string strText = "select *, from BuildVersion bv, ErrorLog er";

            m_MsSqlTextEditor.Text = strText;

            DateTime oDtStart = DateTime.Now;
            List<Suggestion> oSenseList = oSenseBuilder.GetSuggestionsForCursorAt(9);

            List<string> lstStrings = oSenseList.Select(sli => sli.Name).ToList();
            foreach (string strItem in lstStrings)
                Console.WriteLine(strItem);

            Assert.True(lstStrings.Contains("bv.ModifiedDate"));
            Assert.True(lstStrings.Contains("er.ErrorTime"));

        }

        [STAThread]
        [Test]
        public void TestSense_select_join()
        {
            ICSharpCode.MSSQLExtensions.MSSQLTextEditor m_MsSqlTextEditor = new ICSharpCode.MSSQLExtensions.MSSQLTextEditor(m_form);

            SenseBuilder oSenseBuilder = new SenseBuilder(m_MsSqlTextEditor.Document);
            oSenseBuilder.SetComposite(m_DBComposite);

            string strText = "select *, from BuildVersion bv join ErrorLog er";

            m_MsSqlTextEditor.Text = strText;

            DateTime oDtStart = DateTime.Now;
            List<Suggestion> oSenseList = oSenseBuilder.GetSuggestionsForCursorAt(9);

            List<string> lstStrings = oSenseList.Select(sli => sli.Name).ToList();
            foreach (string strItem in lstStrings)
                Console.WriteLine(strItem);

            Assert.True(lstStrings.Contains("bv.ModifiedDate"));
            Assert.True(lstStrings.Contains("er.ErrorTime"));

        }

        [STAThread]
        [Test]
        public void TestSense_select_where()
        {
            ICSharpCode.MSSQLExtensions.MSSQLTextEditor m_MsSqlTextEditor = new ICSharpCode.MSSQLExtensions.MSSQLTextEditor(m_form);

            SenseBuilder oSenseBuilder = new SenseBuilder(m_MsSqlTextEditor.Document);
            oSenseBuilder.SetComposite(m_DBComposite);

            string strText = "select *, from BuildVersion bv where ErrorLog er";

            m_MsSqlTextEditor.Text = strText;

            DateTime oDtStart = DateTime.Now;
            List<Suggestion> oSenseList = oSenseBuilder.GetSuggestionsForCursorAt(9);

            List<string> lstStrings = oSenseList.Select(sli => sli.Name).ToList();
            foreach (string strItem in lstStrings)
                Console.WriteLine(strItem);

            Assert.True(lstStrings.Contains("bv.ModifiedDate"));
            Assert.True(!lstStrings.Contains("er.ErrorTime"));

        }

        [STAThread]
        [Test]
        public void TestSense_select_groupby()
        {
            ICSharpCode.MSSQLExtensions.MSSQLTextEditor m_MsSqlTextEditor = new ICSharpCode.MSSQLExtensions.MSSQLTextEditor(m_form);

            SenseBuilder oSenseBuilder = new SenseBuilder(m_MsSqlTextEditor.Document);
            oSenseBuilder.SetComposite(m_DBComposite);

            string strText = "select *, from BuildVersion bv group by ErrorLog er";

            m_MsSqlTextEditor.Text = strText;

            DateTime oDtStart = DateTime.Now;
            List<Suggestion> oSenseList = oSenseBuilder.GetSuggestionsForCursorAt(9);

            List<string> lstStrings = oSenseList.Select(sli => sli.Name).ToList();
            foreach (string strItem in lstStrings)
                Console.WriteLine(strItem);

            Assert.True(lstStrings.Contains("bv.ModifiedDate"));
            Assert.True(!lstStrings.Contains("er.ErrorTime"));

        }

        [STAThread]
        [Test]
        public void TestSense_select_orderby()
        {
            ICSharpCode.MSSQLExtensions.MSSQLTextEditor m_MsSqlTextEditor = new ICSharpCode.MSSQLExtensions.MSSQLTextEditor(m_form);

            SenseBuilder oSenseBuilder = new SenseBuilder(m_MsSqlTextEditor.Document);
            oSenseBuilder.SetComposite(m_DBComposite);

            string strText = "select *, from BuildVersion bv order by ErrorLog er";

            m_MsSqlTextEditor.Text = strText;

            DateTime oDtStart = DateTime.Now;
            List<Suggestion> oSenseList = oSenseBuilder.GetSuggestionsForCursorAt(9);

            List<string> lstStrings = oSenseList.Select(sli => sli.Name).ToList();
            foreach (string strItem in lstStrings)
                Console.WriteLine(strItem);

            Assert.True(lstStrings.Contains("bv.ModifiedDate"));
            Assert.True(!lstStrings.Contains("er.ErrorTime"));

        }

        [STAThread]
        [Test]
        public void TestSense_select_after_orderby()
        {
            ICSharpCode.MSSQLExtensions.MSSQLTextEditor m_MsSqlTextEditor = new ICSharpCode.MSSQLExtensions.MSSQLTextEditor(m_form);

            SenseBuilder oSenseBuilder = new SenseBuilder(m_MsSqlTextEditor.Document);
            oSenseBuilder.SetComposite(m_DBComposite);

            string strText = "select *, from BuildVersion bv, ErrorLog er order by ";

            m_MsSqlTextEditor.Text = strText;

            DateTime oDtStart = DateTime.Now;
            List<Suggestion> oSenseList = oSenseBuilder.GetSuggestionsForCursorAt(52);

            List<string> lstStrings = oSenseList.Select(sli => sli.Name).ToList();
            foreach (string strItem in lstStrings)
                Console.WriteLine(strItem);

            Assert.True(lstStrings.Contains("bv.ModifiedDate"));
            Assert.True(lstStrings.Contains("er.ErrorTime"));

        }

        [STAThread]
        [Test]
        public void TestSense_select_after_where()
        {
            ICSharpCode.MSSQLExtensions.MSSQLTextEditor m_MsSqlTextEditor = new ICSharpCode.MSSQLExtensions.MSSQLTextEditor(m_form);

            SenseBuilder oSenseBuilder = new SenseBuilder(m_MsSqlTextEditor.Document);
            oSenseBuilder.SetComposite(m_DBComposite);

            string strText =
"select *, from BuildVersion bv, ErrorLog er where ";

            m_MsSqlTextEditor.Text = strText;

            DateTime oDtStart = DateTime.Now;
            List<Suggestion> oSenseList = oSenseBuilder.GetSuggestionsForCursorAt(50);

            List<string> lstStrings = oSenseList.Select(sli => sli.Name).ToList();
            foreach (string strItem in lstStrings)
                Console.WriteLine(strItem);

            Assert.True(lstStrings.Contains("bv.ModifiedDate"));
            Assert.True(lstStrings.Contains("er.ErrorTime"));

        }

        [STAThread]
        [Test]
        public void TestSense_select_after_where_field()
        {
            ICSharpCode.MSSQLExtensions.MSSQLTextEditor m_MsSqlTextEditor = new ICSharpCode.MSSQLExtensions.MSSQLTextEditor(m_form);

            SenseBuilder oSenseBuilder = new SenseBuilder(m_MsSqlTextEditor.Document);
            oSenseBuilder.SetComposite(m_DBComposite);

            string strText =
"select *, from BuildVersion bv, ErrorLog er where er.ErrorTime ";

            m_MsSqlTextEditor.Text = strText;

            DateTime oDtStart = DateTime.Now;
            List<Suggestion> oSenseList = oSenseBuilder.GetSuggestionsForCursorAt(63);

            List<string> lstStrings = oSenseList.Select(sli => sli.Name).ToList();
            foreach (string strItem in lstStrings)
                Console.WriteLine(strItem);

            Assert.True(!lstStrings.Contains("bv.ModifiedDate"));
            Assert.True(!lstStrings.Contains("er.ErrorTime"));

        }

        [STAThread]
        [Test]
        public void TestSense_select_after_where_field_equals()
        {
            ICSharpCode.MSSQLExtensions.MSSQLTextEditor m_MsSqlTextEditor = new ICSharpCode.MSSQLExtensions.MSSQLTextEditor(m_form);

            SenseBuilder oSenseBuilder = new SenseBuilder(m_MsSqlTextEditor.Document);
            oSenseBuilder.SetComposite(m_DBComposite);

            string strText =
"select *, from BuildVersion bv, ErrorLog er where er.ErrorTime = ";

            m_MsSqlTextEditor.Text = strText;

            DateTime oDtStart = DateTime.Now;
            List<Suggestion> oSenseList = oSenseBuilder.GetSuggestionsForCursorAt(65);

            List<string> lstStrings = oSenseList.Select(sli => sli.Name).ToList();
            foreach (string strItem in lstStrings)
                Console.WriteLine(strItem);

            Assert.True(lstStrings.Contains("bv.ModifiedDate"));
            Assert.True(lstStrings.Contains("er.ErrorTime"));

        }

        [STAThread]
        [Test]
        public void TestSense_select_after_where_and()
        {
            ICSharpCode.MSSQLExtensions.MSSQLTextEditor m_MsSqlTextEditor = new ICSharpCode.MSSQLExtensions.MSSQLTextEditor(m_form);

            SenseBuilder oSenseBuilder = new SenseBuilder(m_MsSqlTextEditor.Document);
            oSenseBuilder.SetComposite(m_DBComposite);

            string strText =
"select *, from BuildVersion bv, ErrorLog er where 1=1 and ";

            m_MsSqlTextEditor.Text = strText;

            DateTime oDtStart = DateTime.Now;
            List<Suggestion> oSenseList = oSenseBuilder.GetSuggestionsForCursorAt(58);

            List<string> lstStrings = oSenseList.Select(sli => sli.Name).ToList();
            foreach (string strItem in lstStrings)
                Console.WriteLine(strItem);

            Assert.True(lstStrings.Contains("bv.ModifiedDate"));
            Assert.True(lstStrings.Contains("er.ErrorTime"));

        }

        [STAThread]
        [Test]
        public void TestSense_select_from()
        {
            ICSharpCode.MSSQLExtensions.MSSQLTextEditor m_MsSqlTextEditor = new ICSharpCode.MSSQLExtensions.MSSQLTextEditor(m_form);

            SenseBuilder oSenseBuilder = new SenseBuilder(m_MsSqlTextEditor.Document);
            oSenseBuilder.SetComposite(m_DBComposite);

            string strText =
"select *, from ";

            m_MsSqlTextEditor.Text = strText;

            DateTime oDtStart = DateTime.Now;
            List<Suggestion> oSenseList = oSenseBuilder.GetSuggestionsForCursorAt(15);

            List<string> lstStrings = oSenseList.Select(sli => sli.Name).ToList();
            foreach (string strItem in lstStrings)
                Console.WriteLine(strItem);

            Assert.True(lstStrings.Contains("BuildVersion"));
            Assert.True(lstStrings.Contains("ErrorLog"));

        }

        [STAThread]
        [Test]
        public void TestSense_select_schema()
        {
            ICSharpCode.MSSQLExtensions.MSSQLTextEditor m_MsSqlTextEditor = new ICSharpCode.MSSQLExtensions.MSSQLTextEditor(m_form);

            SenseBuilder oSenseBuilder = new SenseBuilder(m_MsSqlTextEditor.Document);
            oSenseBuilder.SetComposite(m_DBComposite);

            string strText =
"select *, from SalesLT.Address sales ";



            m_MsSqlTextEditor.Text = strText;

            DateTime oDtStart = DateTime.Now;
            List<Suggestion> oSenseList = oSenseBuilder.GetSuggestionsForCursorAt(10);

            List<string> lstStrings = oSenseList.Select(sli => sli.Name).ToList();
            foreach (string strItem in lstStrings)
                Console.WriteLine(strItem);

            Assert.True(lstStrings.Contains("sales.AddressID"));


        }

        [STAThread]
        [Test]
        public void TestSense_select_after_field()
        {
            ICSharpCode.MSSQLExtensions.MSSQLTextEditor m_MsSqlTextEditor = new ICSharpCode.MSSQLExtensions.MSSQLTextEditor(m_form);

            SenseBuilder oSenseBuilder = new SenseBuilder(m_MsSqlTextEditor.Document);
            oSenseBuilder.SetComposite(m_DBComposite);

            string strText =
"select sales.AddressID from SalesLT.Address sales ";



            m_MsSqlTextEditor.Text = strText;

            DateTime oDtStart = DateTime.Now;
            List<Suggestion> oSenseList = oSenseBuilder.GetSuggestionsForCursorAt(23);


            List<string> lstStrings = oSenseList.Select(sli => sli.Name).ToList();
            foreach (string strItem in lstStrings)
                Console.WriteLine(strItem);

            Assert.True(oSenseList.Count == 0);

        }

        [STAThread]
        [Test]
        public void TestSense_select_after_table()
        {
            ICSharpCode.MSSQLExtensions.MSSQLTextEditor m_MsSqlTextEditor = new ICSharpCode.MSSQLExtensions.MSSQLTextEditor(m_form);

            SenseBuilder oSenseBuilder = new SenseBuilder(m_MsSqlTextEditor.Document);
            oSenseBuilder.SetComposite(m_DBComposite);

            string strText =
"select sales.AddressID from SalesLT.Address sales ";

            m_MsSqlTextEditor.Text = strText;

            DateTime oDtStart = DateTime.Now;
            List<Suggestion> oSenseList = oSenseBuilder.GetSuggestionsForCursorAt(52);


            List<string> lstStrings = oSenseList.Select(sli => sli.Name).ToList();
            foreach (string strItem in lstStrings)
                Console.WriteLine(strItem);

            Assert.True(oSenseList.Count == 0);

        }

        [STAThread]
        [Test]
        public void TestSense_select_after_view_with_alias()
        {
            ICSharpCode.MSSQLExtensions.MSSQLTextEditor m_MsSqlTextEditor = new ICSharpCode.MSSQLExtensions.MSSQLTextEditor(m_form);

            SenseBuilder oSenseBuilder = new SenseBuilder(m_MsSqlTextEditor.Document);
            oSenseBuilder.SetComposite(m_DBComposite);

            string strText =
"select  from SalesLT.vGetAllCategories vw ";

            m_MsSqlTextEditor.Text = strText;

            DateTime oDtStart = DateTime.Now;
            List<Suggestion> oSenseList = oSenseBuilder.GetSuggestionsForCursorAt(7);


            List<string> lstStrings = oSenseList.Select(sli => sli.Name).ToList();
            foreach (string strItem in lstStrings)
                Console.WriteLine(strItem);

            Assert.True(lstStrings.Contains("vw.ProductCategoryID"));


        }

        [STAThread]
        [Test]
        public void TestSense_select_after_view()
        {
            ICSharpCode.MSSQLExtensions.MSSQLTextEditor m_MsSqlTextEditor = new ICSharpCode.MSSQLExtensions.MSSQLTextEditor(m_form);

            SenseBuilder oSenseBuilder = new SenseBuilder(m_MsSqlTextEditor.Document);
            oSenseBuilder.SetComposite(m_DBComposite);

            string strText =
"select  from SalesLT.vGetAllCategories ";

            m_MsSqlTextEditor.Text = strText;

            DateTime oDtStart = DateTime.Now;
            List<Suggestion> oSenseList = oSenseBuilder.GetSuggestionsForCursorAt(7);


            List<string> lstStrings = oSenseList.Select(sli => sli.Name).ToList();
            foreach (string strItem in lstStrings)
                Console.WriteLine(strItem);

            Assert.True(lstStrings.Contains("ProductCategoryID"));


        }

        [STAThread]
        [Test]
        public void TestSense_select_select()
        {
            ICSharpCode.MSSQLExtensions.MSSQLTextEditor m_MsSqlTextEditor = new ICSharpCode.MSSQLExtensions.MSSQLTextEditor(m_form);

            SenseBuilder oSenseBuilder = new SenseBuilder(m_MsSqlTextEditor.Document);
            oSenseBuilder.SetComposite(m_DBComposite);

            string strText =
"select  from BuildVersion bv select * from ErrorLog er ";

            m_MsSqlTextEditor.Text = strText;

            DateTime oDtStart = DateTime.Now;
            List<Suggestion> oSenseList = oSenseBuilder.GetSuggestionsForCursorAt(7);


            List<string> lstStrings = oSenseList.Select(sli => sli.Name).ToList();
            foreach (string strItem in lstStrings)
                Console.WriteLine(strItem);

            Assert.True(lstStrings.Contains("bv.ModifiedDate"));
            Assert.False(lstStrings.Contains("er.ErrorTime"));


        }


        [STAThread]
        [Test]
        public void TestSense_select_no_alias()
        {
            ICSharpCode.MSSQLExtensions.MSSQLTextEditor m_MsSqlTextEditor = new ICSharpCode.MSSQLExtensions.MSSQLTextEditor(m_form);

            SenseBuilder oSenseBuilder = new SenseBuilder(m_MsSqlTextEditor.Document);
            oSenseBuilder.SetComposite(m_DBComposite);

            string strText =
"select * from BuildVersion where ";

            m_MsSqlTextEditor.Text = strText;

            DateTime oDtStart = DateTime.Now;
            List<Suggestion> oSenseList = oSenseBuilder.GetSuggestionsForCursorAt(33);


            List<string> lstStrings = oSenseList.Select(sli => sli.Name).ToList();
            foreach (string strItem in lstStrings)
                Console.WriteLine(strItem);

            Assert.True(lstStrings.Contains("ModifiedDate"));

        }

        [STAThread]
        [Test]
        public void TestSense_join_no_alias()
        {
            ICSharpCode.MSSQLExtensions.MSSQLTextEditor m_MsSqlTextEditor = new ICSharpCode.MSSQLExtensions.MSSQLTextEditor(m_form);

            SenseBuilder oSenseBuilder = new SenseBuilder(m_MsSqlTextEditor.Document);
            oSenseBuilder.SetComposite(m_DBComposite);

            string strText =
"select * from BuildVersion join ErrorLog on ";

            m_MsSqlTextEditor.Text = strText;

            DateTime oDtStart = DateTime.Now;
            List<Suggestion> oSenseList = oSenseBuilder.GetSuggestionsForCursorAt(44);


            List<string> lstStrings = oSenseList.Select(sli => sli.Name).ToList();
            foreach (string strItem in lstStrings)
                Console.WriteLine(strItem);

            Assert.True(lstStrings.Contains("BuildVersion.ModifiedDate"));
            Assert.True(lstStrings.Contains("ErrorLog.ErrorTime"));

        }


    }
}
